<?php
/*
	Version   :  2.7
	Created   :  20090211
	Modified  :  20100803
	Author    :  Pablo Ramirez
	co-Author :
	Descr     : Controlador "Invoice"
	Changelog : 0.1 - Version en Desarrollo
				1.0 - Version Inicial
				1.1 - Modificacion para agrupar por depot y service_type
				1.2 - Creacion de Reportes Excel
				1.3 - Mejoras en Reportes Excel, cambio a version 1.1 de php-excel.class y mejoras solicitadas
				1.4 - Control de Acceso
				1.5 - Mejoras en reporte, suma de totales, cuenta de registros y separacion por service_type
				1.6 - Se agrega cou_codigo a la busqueda de price_list
				1.7 - Se soluciona filtrado de containers MACH que no tienen BOX en reporte
				1.8 - Se agrega validacion de perfil para mostrar precios
				2.0 - Se realizan las modificaciones que solucionan el correcto agrupamiento por survey, codigo contenedor y service
				2.1 - Se cambia comparacion a k>0 para detectar cambio en grupo para excel en report2
				2.2 - Se agregan ord_codigo y comments de order_estimates a report2
				2.3 - Se complementa detalle de nombre de deposito y descripcion de service para planilla resume
				2.4 - Se agrega funcionalidad para confirmacion de facturas
				2.5 - Se corrige acumulacion de detalle trans-survey_type en metodo create
				2.7 - Se separan los reportes excel en archivos distintos
*/
class ManagementController extends AppController
{	
	var $name       = 'Managements';
	var $uses       = array('Order','SurveyType', 'OrderEstimate', 'Owner', 'OwnerPrice', 'Country', 'Depot');
	var $helpers    = array('Html','Javascript','Ajax');
	var $estado_informada         = 'N';
	var $estado_inpeccionada      = 'I';
	var $layout		= 'main';
	
	function index() {
		$this->checkSession();
		$survey_types_general = $this->SurveyType->generateList("sut_codigo NOT LIKE '%-S'");
		if (!empty($survey_types_general)) {
				$this->set('survey_types_general', array('A'=>'Select a Survey Type')+$survey_types_general);
		}
		$owners = $this->Owner->generateList();
		if (!empty($owners)) {
				$this->set('owners', array('A'=>'Select an Owner')+$owners);
		}
		$this->set('years', array(2009=>2009,2010=>2010,2011=>2011,2012=>2012));
	}
	
	function create() {
		$this->checkSession();
		$usr_profile = $this->Session->read('Profile');
		$this->layout = 'ajax';
		$ERROR = 0;
		if (!empty($this->data)) {
			$own_codigo = $this->data['Owner']['own_codigo'];
			$own_nombre = $this->Owner->field('own_nombre', "own_codigo = '$own_codigo'");
			$sut_codigo = $this->data['Management']['sut_codigo'];
			$year = $this->data['Management']['year'];
			$from_date = $year . '-01-01 00:00:00';
			
			$to_date = $year . '-12-31';
			
			if ($own_codigo == 'A') {
				$ERROR = 1;
				$this->set('ERROR_DESC', 'An Owner must be specified');
				//$this->render('error','ajax');
			}
			
			if ($sut_codigo == 'A') {
				$ERROR = 2;
				$this->set('ERROR_DESC', 'A Survey Type must be specified');
				//$this->render('error','ajax');
			}
			
			
			$actual_month = date("m");
			$actual_year = date("Y");
			//$to_date = $year . '-' . $past_month . '-31';
			
			$months = array('01','02','03','04','05','06','07','08','09','10','11','12');
			
			//Summary por Mes
			$summary = array();
				
			if ($ERROR == 0) {	
				foreach ($months as $month) { 
					//2.6 Se agrega condicion para terminar el ciclo para el mes actual
					if (($month === $actual_month) and ($year === $actual_year)) { break; }
					$from = $year . '-' . $month . '-' . '01 00:00:00';
					$to   = $year . '-' . $month . '.' . '31 23:59:59';
					
					switch ($month) {
						case '01': $l_month='JANUARY'; break;;
						case '02': $l_month='FEBRUARY'; break;;
						case '03': $l_month='MARCH'; break;;
						case '04': $l_month='APRIL'; break;;
						case '05': $l_month='MAY'; break;;
						case '06': $l_month='JUNE'; break;;
						case '07': $l_month='JULY'; break;;
						case '08': $l_month='AUGUST'; break;;
						case '09': $l_month='SEPTEMBER'; break;;
						case '10': $l_month='OCTOBER'; break;;
						case '11': $l_month='NOVEMBER'; break;;
						case '12': $l_month='DECEMBER'; break;;
						default:   $l_month='Not valid Month'; break;;
					}
					
					//echo "from: ". $from . " - to: " . $to ."<br />";
					
					$orders = $this->OrderEstimate->getEstimatesByMonth($own_codigo, $from, $to, $sut_codigo);
					//pr($orders);
					//hacer que detecte cuando $orders no encontro ordenes
					/*if (!array_key_exists('0', $orders)) {
						$ERROR = 2;
						$this->set('ERROR_DESC', 'There were no orders');
						break;
					}*/
					//array_push($summary,array($month=>$orders));
					$summary[$l_month] = $orders;
				}
			}
			
			if ($ERROR == 0) {
				//Summary por Depositos
				$depots = $this->Depot->findAll(null, 'dep_codigo');
				$summary_per_depots= array();	
				foreach ($depots as $depot) {
					$dep_codigo = $depot['Depot']['dep_codigo'];
					foreach ($months as $month) { 
						if (($month === $actual_month) and ($year === $actual_year)) { break; }
						$from = $year . '-' . $month . '-' . '01 00:00:00';
						$to   = $year . '-' . $month . '.' . '31 23:59:59';
						
						switch ($month) {
							case '01': $l_month='JANUARY'; break;;
							case '02': $l_month='FEBRUARY'; break;;
							case '03': $l_month='MARCH'; break;;
							case '04': $l_month='APRIL'; break;;
							case '05': $l_month='MAY'; break;;
							case '06': $l_month='JUNE'; break;;
							case '07': $l_month='JULY'; break;;
							case '08': $l_month='AUGUST'; break;;
							case '09': $l_month='SEPTEMBER'; break;;
							case '10': $l_month='OCTOBER'; break;;
							case '11': $l_month='NOVEMBER'; break;;
							case '12': $l_month='DECEMBER'; break;;
							default:   $l_month='Not valid Month'; break;;
						}
						
						$orders = $this->OrderEstimate->getEstimatesByDepot($own_codigo, $dep_codigo, $from, $to, $sut_codigo);
						if ($orders['qty'] > 0) {
							$summary_per_depots[$dep_codigo][$l_month] = $orders;
						}
					}
				}
			}
			
			if ($ERROR == 0) {	
				//Summary por Paises
				$countries = $this->Country->findAll(null, 'cou_codigo');
				$summary_per_countries= array();
				foreach ($countries as $country) {
					//if (($month === $actual_month) and ($year === $actual_year)) { break; }
					$from = $year . '-' . '01' . '-' . '01 00:00:00';
					if ($year === $actual_year) {
						$to   = $year . '-' . $actual_month . '.' . '01 00:00:00';	
					} else {
						$to   = $year . '-' . '12' . '-' . '31 23:59:59';
					}
					
					$cou_codigo = $country['Country']['cou_codigo'];
					$depots = $this->Depot->findAll("dep_country = '$cou_codigo'", 'dep_nombre');
					
					foreach ($depots as $depot) {
						$orders = $this->OrderEstimate->getEstimatesByCountry($own_codigo, $cou_codigo, $from, $to, $sut_codigo, $depot['Depot']['dep_nombre']);
						
						if ($orders['qty'] > 0) {
							$l_depot = $depot['Depot']['dep_nombre'];
							$summary_per_countries[$cou_codigo][$l_depot] = $orders;
						}
					}
				}
			}
			
			//Finalizacion
			if ($ERROR == 0) {	
				$this->set('summary', $summary);
				$this->set('summary_per_depots', $summary_per_depots);
				$this->set('summary_per_countries', $summary_per_countries);
				$this->set('own_nombre', $own_nombre);
				$this->set('sut_codigo', $sut_codigo);
				$this->set('year', $year);
				$this->render('management','ajax');
				$this->Session->write('summary', $summary);
				$this->Session->write('summary_per_depots', $summary_per_depots);
				$this->Session->write('summary_per_countries', $summary_per_countries);
			} else {
				$this->render('error','ajax');
			}
		}
	}	

	function export() {
		$this->checkSession();
		$this->layout = 'blank';
		require (VENDORS . "php-excel.class.php");
	
		//$summary_type = $this->data['Export']['summary_type'];
		//pr($this->data);
		//break;
		
		$own_nombre = htmlentities($this->data['Export']['own_nombre'], ENT_COMPAT);
		$summary_type = $this->data['Export']['summary_type'];
		
		$k = 0;
		$rows = array();
				
		switch ($summary_type) {
			case 'per_month':
				$summary = $this->Session->read('summary');
				$data_title = 'Summary per Month';
				$file_name =  $this->data['Export']['sut_codigo'] . '_' . $this->data['Export']['year'] . '_' . 'per_months';
				$rows[$k] = array("Month", "Quantity", "Initial Estimate USD", "Final Estimate USD", "Reduction USD", "Reduction %", "Average USD");
			 	$k++;
			 	$offset = 0;
				while ($row = current($summary)) {
			 		//echo key($summary).'<br />';
			 		$key = key($summary);
			 		//echo $key;
			 		//pr($summary);
			 		if ($summary[$key]['qty'] == 0) {
			 			$summary[$key]['estimate_initial'] = 0;
			 			$summary[$key]['estimate_final'] = 0;
			 			$reduction_usd = 0;
			 			$reduction_perc = 0;
			 			$average = "";
			 		} else {
			 			$reduction_usd = "=RC[-2]-RC[-1]";
			 			$reduction_perc = "=ROUND(RC[-1]/RC[-3]*100,2)";
			 			$average = "=ROUND(RC[-3]/RC[-5],2)";
			 		}
			 		$rows[$k] = array($key, $summary[$key]['qty'], $summary[$key]['estimate_initial'], $summary[$key]['estimate_final'], $reduction_usd, $reduction_perc, $average);
			 		$k++;
			 		$offset++;
					next($summary);
				}
				$rows[$k] = array("TOTAL", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=ROUND(AVERAGE(R[-". $offset ."]C:R[-1]C),2)");
				
				$detalle_summary = $rows;
				//pr($detalle_summary);
				break;
			case 'per_depot_month':
				$summary = $this->Session->read('summary_per_depots');
				$data_title = 'Summary per Depot/Month';
				$file_name =  $this->data['Export']['sut_codigo'] . '_' . $this->data['Export']['year'] . '_' . 'per_depots';
				
				while ($row = current($summary)) {
			 		$key = key($summary);
			 		$rows[$k] = array($key);
			 		$k++;
			 		$rows[$k] = array("Depot", "Quantity", "Initial Estimate USD", "Final Estimate USD", "Reduction USD", "Reduction %", "Average USD");
			 		$k++;
			 		$offset = 0;
			 		foreach ($row as $month=>$data) {
				 		if ($data['qty'] == 0) {
				 			$data['estimate_initial'] = 0;
				 			$data['estimate_final'] = 0;
				 			$reduction_usd = 0;
				 			$reduction_perc = 0;
				 			$average = "";
				 		} else {
				 			$reduction_usd = "=RC[-2]-RC[-1]";
				 			$reduction_perc = "=ROUND(RC[-1]/RC[-3]*100,2)";
				 			$average = "=ROUND(RC[-3]/RC[-5],2)";
				 		}
			 			$rows[$k] = array($month, $data['qty'], $data['estimate_initial'], $data['estimate_final'], $reduction_usd, $reduction_perc, $average);
			 			$k++;
			 			$offset++;
			 		}
			 		$rows[$k] = array("TOTAL", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=ROUND(AVERAGE(R[-". $offset ."]C:R[-1]C),2)");
			 		$k++;
			 		$rows[$k] = array("");
					next($summary);
					$k++;
				}
				$detalle_summary = $rows;
				//pr($summary);
				break;
			case 'per_country_depot':
				$summary = $this->Session->read('summary_per_countries');
				$data_title = 'Summary per Country/Depot';
				$file_name =  $this->data['Export']['sut_codigo'] . '_' . $this->data['Export']['year'] . '_' . 'per_countries';
				
				while ($row = current($summary)) {
			 		$key = key($summary);
			 		$rows[$k] = array($key);
			 		$k++;
			 		$rows[$k] = array("Depot", "Quantity", "Initial Estimate USD", "Final Estimate USD", "Reduction USD", "Reduction %", "Average USD");
			 		$k++;
			 		$offset = 0;
			 		foreach ($row as $country=>$data) {
			 			
			 			if ($data['qty'] == 0) {
				 			$data['estimate_initial'] = 0;
				 			$data['estimate_final'] = 0;
				 			$reduction_usd = 0;
				 			$reduction_perc = 0;
				 			$average = "";
				 		} else {
				 			$reduction_usd = "=RC[-2]-RC[-1]";
				 			$reduction_perc = "=ROUND(RC[-1]/RC[-3]*100,2)";
				 			$average = "=ROUND(RC[-3]/RC[-5],2)";
				 		}
			 			$dep_codigo = substr($data['depot'], 0, strpos($data['depot'], "|")-2);
			 			$rows[$k] = array($dep_codigo, $data['qty'], $data['estimate_initial'], $data['estimate_final'], $reduction_usd, $reduction_perc, $average);
			 			$k++;
			 			$offset++;
			 		}
			 		$rows[$k] = array("TOTAL", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=SUM(R[-". $offset ."]C:R[-1]C)", "=ROUND(AVERAGE(R[-". $offset ."]C:R[-1]C),2)");
			 		//pr($summary);
			 		$k++;
			 		$rows[$k] = array("");
					next($summary);
					$k++;
				}
				$detalle_summary = $rows;
				//pr($summary);
			break;
		}
		
		/*
			pr($summary);
			pr($summary_per_depots);
			pr($summary_per_countries);
			break;
		*/
		
		$resumen = array (
		    1 => array ("Owner :", $own_nombre),
		    2 => array ("Inspeccion :", $this->data['Export']['sut_codigo']),
			3 => array ("A&#241;o :", $this->data['Export']['year']),
			4 => array ("",""),
			5 => array ("","")
		    );
		    
		$item1 = array (
			1 => array ("",""),
			2 => array ($data_title)
			);
				
		$xls = new Excel_XML('ISO-8859-1', true, $data_title);
		$xls->addArray ( $resumen );
		$xls->addArray ( $item1 );
		$xls->addArray ( $detalle_summary );
		$xls->generateXML ($file_name);
	}
}